if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[GetCOIUserHierarchyList]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[GetCOIUserHierarchyList]
GO
SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO
/******************************************************************                      
* Name:   [GetCOIUserHierarchyList]                      
* Purpose:  Retrieves matching records(joining with admperson,conflictofinterest tables).          
*                      
* PARAMETERS(IN)                      
* Name          Description                           
* -------------      -------------------------------------------                      
* @xml       xml Document containing list of Ids(REQUIRED)                        
*********************************************************************/                      
                      
CREATE PROCEDURE [dbo].[GetCOIUserHierarchyList]                      
(                      
@xml text                      
)                      
AS BEGIN                      
SET NOCOUNT ON                      
                      
 declare @xmlDoc int                        
 declare @admPersonIds table (Sequence int identity, [ID] int,[SupervisorId] int)                        
 exec sp_xml_preparedocument @xmlDoc output, @xml                        
 insert into @admPersonIds select [Id],[SupervisorId] from openxml(@xmlDoc, '//IdList', 2) with (Id int,SupervisorId int)                        
 exec sp_xml_removedocument @xmlDoc                      
                      
SELECT adp.[Id],                      
     adp.[lastname]+', '+adp.[firstname] as [Name],                      
     adp1.[lastname]+', '+adp1.[firstName] as [Supervisor],                    
	 coi.[Id] as [COIId],                    
     coi.[PolicyViolation],                      
     coi.[HasOutsideActivities],                      
     coi.[HasEquityInterests],                      
     coi.[HasFamilyAssociations],                      
     coi.[OtherActivity],                      
     coi.[LastNotified],                      
     coi.[DateSubmitted],                      
     coi.[DateReviewed],        
	 coi.[Status],      
	 rc.[RefMeaning] as [COIStatusText],
	 coi.[Royalty]                   
                     
  FROM @admPersonIds tempTable                  
  Inner JOIN AdmPerson adp                   
  on tempTable.[Id] =adp.[Id]                  
  Left Outer JOIN ConflictOfInterest coi                   
  on tempTable.[Id]= coi.[admPersonId]                  
  AND coi.[Id]= (Select Max(Id)as LatestCOIId from conflictofinterest where admPersonId=adp.[Id] and status <> 'COISTATUS_TERMINATED' AND COITypeId = 1)                  
  Left Outer Join AdmPerson adp1                   
  on adp1.[Id] =tempTable.[SupervisorId]            
  Left join RefCode rc      
  on rc.RefDomain = 'COISTATUS'      
  And rc.RefCode = coi.Status                      
      
END          